Grokking Database Fundamentals for Tech Interviews
Rate this course
Ask Author
Contribute
Back to course home

0% completed

Vote For New Content
Introduction to Database Systems
Course Overview
Overview of Databases
Core Components of Database
Database Architectures
Relational Vs. Non-relational Databases
Data Storage in DBMS
Data Storage Fundamentals
File Organization in DBMS
Data Compression and Encoding
Quiz
Indexing in DBMS
Introduction to Database Indexes
Single Level Indexing
Tree-Based Indexing Techniques
Hash-Based Indexing
Bloom Filters
Quiz
Transaction Management
Understanding the Transaction
Atomicity
Consistency
Isolation
Durability
Quiz
Concurrency Control
Understanding the Concurrency Control
Optimistic vs. Pessimistic Concurrency Control
Lock-Based Concurrency Control
Timestamp-Based Concurrency Control
Multiversion Concurrency Control (MVCC)
Quiz
Fundamentals of Distributed Databases
Partitioning in Databases
Partitioning Types
Sharding in Databases
Distributed Query Processing
Quiz
Replication in Databases
Replication Topologies
Understanding the Consistency Models in Databases
The CAP Theorem and Its Implications
Quiz
Understanding the Consensus
Consensus algorithms
Leader Election Strategies
Quiz
Understanding the Fault Tolerance
Logging Mechanisms in Databases
Checkpointing and Recovery Basics
Coordinated Recovery in Distributed Systems
High Availability Through Replication and Failover
Ensuring Data Resilience: Redundancy, Mirroring, and Recovery
Quiz
Introduction to NoSQL
Key-Value Stores
Document-Oriented Databases
Column-Family and Wide-Column Stores
Graph Databases
Quiz
Quora System Design & Database Design
TikTok System Design & Database Design
Airbnb System Design & Database Design
Snapchat System Design & Database Design
Understanding the Concurrency Control

What is Concurrency Control?

Image

Concurrency control is a mechanism used in databases to ensure the correct execution of transactions when multiple users or processes are accessing and modifying the database simultaneously. It ensures:

  1. Data Integrity: Prevents inconsistencies caused by concurrent access to the same data.
  2. Isolation: Ensures that each transaction operates as if it is the only one running.
  3. Performance: Maximizes the system’s ability to handle multiple transactions efficiently.

Why is Concurrency Control Important?

Without proper concurrency control, the following issues can arise:

  1. Dirty Reads:

    • Reading uncommitted changes made by another transaction.
    • Example: A transaction reads a value that is later rolled back, leading to inconsistencies.
  2. Lost Updates:

    • Multiple transactions overwrite each other's changes.
    • Example: Two users update the same bank account balance, and one update is lost.
  3. Nonrepeatable Reads:

    • A transaction reads the same data twice and gets different results because another transaction modified it in between.
    • Example: Checking inventory count before and after another user updates it.
  4. Phantom Reads:

    • A transaction reads a set of rows, and another transaction inserts or deletes rows, causing the first transaction to see a different result.
    • Example: A query for available meeting rooms shows different results after another user books a room.

Concurrency Control Techniques

Databases use various techniques to manage concurrency and prevent the issues mentioned above:

Image

1. Optimistic Concurrency Control

  • Assumes conflicts are rare and allows transactions to execute without locks.

  • Before committing, the database checks for conflicts:

    • If no conflicts are found, the transaction commits.
    • If conflicts are detected, the transaction is rolled back and retried.
  • Example:

    • Two users edit the same document. If no overlapping changes are found, both edits are saved.

2. Locking Mechanisms

  • Locks restrict access to data during a transaction, ensuring data consistency.
  • Example:
    • Transaction A locks a row to update it.
    • Transaction B must wait until Transaction A releases the lock.

3. Timestamp Ordering

  • Transactions are assigned timestamps when they begin.
  • Transactions are executed in the order of their timestamps to avoid conflicts.
  • Ensures serializability, making transactions appear as if they were executed one at a time.
  • Example:
    • If Transaction A starts at 10:00 AM and Transaction B starts at 10:01 AM, A will execute first.

4. Multiversion Concurrency Control (MVCC)

  • Maintains multiple versions of data for concurrent transactions.
  • Each transaction works on its own version, ensuring isolation.
  • Changes are visible only after the transaction commits.
  • Commonly used in databases like PostgreSQL.
  • Example:
    • Transaction A reads the old version of a row while Transaction B updates it. A will see the old value until B commits.

Deadlocks in Concurrency Control

A deadlock occurs when two or more transactions wait for each other’s locks, preventing progress.

Image

Example:

  1. Process A locks Resource 1 and needs Resource 2.
  2. Process B locks Resource 2 and needs Resource 1.
  3. Both transactions wait indefinitely.

Deadlock Resolution:

  1. Timeouts: Automatically abort transactions that wait too long.
  2. Deadlock Detection: The database identifies and aborts one of the conflicting transactions.

Real-World Applications of Concurrency Control

  1. Banking Systems:

    • Prevents double withdrawals or inconsistent balance updates.
    • Example: Ensures that two users cannot withdraw the same amount simultaneously.
  2. E-Commerce:

    • Manages inventory updates and prevents overselling.
    • Example: Locks a product row during purchase to update stock levels.
  3. Collaborative Tools:

    • Ensures consistent edits in shared documents.
    • Example: Multiple users editing a Google Doc simultaneously.
Quiz
Optimistic vs. Pessimistic Concurrency Control
Mark as Completed

On this page

What is Concurrency Control?

Why is Concurrency Control Important?

Concurrency Control Techniques

  1. Optimistic Concurrency Control
  1. Locking Mechanisms
  1. Timestamp Ordering
  1. Multiversion Concurrency Control (MVCC)

Deadlocks in Concurrency Control

Example:

Deadlock Resolution:

Real-World Applications of Concurrency Control